'******************************************************************************* 
' * Web Site web-based Installer 
' * 
' * Author: King Wilder 
' * Email: info@kingwilder.com 
' * Date updated: 3/3/2008 
' * 
' * This is a simple-to-use web-based installer that you can plug into your new 
' * web application that allows you to create the database in SQL Server and all 
' * the tables and create the admin user and roles. 
' * 
' * Much of this code was borrowed from an early version of the installer used in dashCommerce. 
' * http://www.dashCommerce.org 
' * 
' * Version 1.0.0.0 
' * Version 1.1.0.0 Updated and converted to vb.net for Time Tracker 
' *   By Kent Ogletree
' * **************************************************************************** 


Imports System 
Imports System.Data 
Imports System.Data.SqlClient 
Imports System.Configuration 
Imports System.Collections 
Imports System.Web 
Imports System.Web.Security 
Imports System.Web.UI 
Imports System.Web.UI.WebControls 
Imports System.Web.UI.WebControls.WebParts 
Imports System.Web.UI.HtmlControls 
Imports System.IO 
Imports System.Web.Configuration 
Imports System.Text
Imports ASPNET.StarterKit.Setup

Public Partial Class install_install 
    Inherits System.Web.UI.Page 
    
    #Region "Constants" 
    Private Const SCRIPT_TABLES As String = "~/Install/InstallScripts/timetracker.sql"
    ' Passwords tables 
    Private Const SYS_ADMINISTRATORS_ROLE As String = "SystemAdministrator"
    Private Const ADMINISTRATORS_ROLE As String = "ProjectAdministrator"
    Private Const PROJECTMANGER_ROLE As String = "ProjectManager"
    Private Const USERS_ROLE As String = "Consultant"
    
    ' Other possible scripts that can be run. You need to provide these scripts when you distribute 
    ' the application, but you can add as many as you want. They would be implemented in the RunScripts() 
    ' method. 
    'private const string SCRIPT_SAMPLE_DATA = "~/Install/InstallScripts/SampleData.sql"; // Sample Data 
    'private const string SCRIPT_DROP = "~/Install/InstallScripts/Drop.sql"; 
    
    #End Region 
    
    #Region "Events" 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) 
        lblError.Text = String.Empty 
        
    End Sub 
    
    
    Protected Sub wizInstall_FinishButtonClick(ByVal sender As Object, ByVal e As WizardNavigationEventArgs) 
        Try 
            'System.Threading.Thread.Sleep(3000); 
            RunAll() 
        Catch ex As Exception 
            ShowError(ex.Message) 
            wizInstall.ActiveStepIndex = wizInstall.ActiveStepIndex - 1 
        End Try 
    End Sub 
    
    Protected Sub wizInstall_NextButtonClick(ByVal sender As Object, ByVal e As WizardNavigationEventArgs) 
    End Sub 
    
    
    Protected Sub wizInstall_PreviousButtonClick(ByVal sender As Object, ByVal e As WizardNavigationEventArgs) 
        
    End Sub 
    
    Protected Sub wizInstall_ActiveStepChanged(ByVal sender As Object, ByVal e As EventArgs) 
        Select Case wizInstall.ActiveStepIndex 
            Case 0 
                ' Welcome 
                webConfigLocation.Text = Server.MapPath("~/web.config") 
                Exit Select 
            Case 1 
                ' Display DB Credentials... 
                
                Exit Select 
            Case 2 
                ' Save DB Credentials from previous step 
                ' Display Database settings 
                
                Try 
                    
                    If Not chkTrustedConnection.Checked Then 
                        If String.IsNullOrEmpty(txtDBUsername.Text.Trim()) Then 
                            Throw New InvalidOperationException("Username cannot be null or empty string.") 
                        End If 
                        If String.IsNullOrEmpty(txtDBPassword.Text.Trim()) Then 
                            Throw New InvalidOperationException("Password cannot be null or empty string.") 
                        End If 
                        ViewState("DbUserName") = txtDBUsername.Text.Trim() 
                        ViewState("DbPassword") = txtDBPassword.Text.Trim() 
                    End If 
                    
                    ' Load the database DropDownList control with database names. 
                    BindDbList() 
                Catch ex As Exception 
                    wizInstall.ActiveStepIndex = wizInstall.ActiveStepIndex - 1 
                    ShowError(ex.Message) 
                End Try 
                
                Exit Select 
            Case 3 
                ' Save Database settings from previous step 
                ' Display Admin Account settings 
                
                If (ddlDbList.SelectedValue = "-1") AndAlso (String.IsNullOrEmpty(txtDBName.Text.Trim())) Then 
                    wizInstall.ActiveStepIndex = wizInstall.ActiveStepIndex - 1 
                    ShowError("Please select a database or supply a new database name.") 
                End If 
                If ddlDbList.SelectedValue <> "-1" Then 
                    ViewState("DBName") = ddlDbList.SelectedValue 
                    ViewState("UseExistingDb") = True 
                End If 
                If ddlDbList.SelectedValue = "-1" AndAlso txtDBName.Text.Trim() <> String.Empty Then 
                    ViewState("DBName") = txtDBName.Text.Trim() 
                    ViewState("UseExistingDb") = False 
                End If 
                
                ' This will load sample data into the fields for testing. 
                ' Comment out or delete the method when going into production. 
                'LoadSampleValues(); 
                Exit Select 
            Case 4 
                ' Confirmation Step 
                AttachEventHandlerToFinishButton() 
                
                ' Save admin information. 
                CollectAdminInfo() 
                
                ' Display all collected information. 
                lblServerName.Text = txtServerName.Text 
                lblDatabaseName.Text = ViewState("DBName").ToString() 
                If Not chkTrustedConnection.Checked Then 
                    rowDbUsername.Visible = True 
                    rowDbPassword.Visible = True 
                    lblDBUserName.Text = txtDBUsername.Text 
                    lblDBPassword.Text = ViewState("DbPassword").ToString() 
                Else 
                    rowDbUsername.Visible = False 
                    rowDbPassword.Visible = False 
                End If 
                
                lblAdminUsername.Text = txtAdminUserName.Text 
                lblAdminPassword.Text = ViewState("AdminPassword").ToString() 
                lblAdminEmail.Text = txtAdminEmail.Text 
                lblAdminSecretAnswer.Text = txtSecretAnswer.Text 
                lblAdminSecretQuestion.Text = txtSecretQuestion.Text 
                
                ' Update the web.config file 
                WriteConnectionStringsToConfig() 
                
                Exit Select 
            Case 5 
                ' Completed 
                ' Now run everything... activated in the Finish button click. 
                
                Exit Select 
            Case Else 
                Exit Select 
        End Select 
    End Sub 
    
    #End Region 
    
    #Region "Private Methods" 
    
    ''' <summary> 
    ''' Run all the functions to install the database tables and admin account. 
    ''' </summary> 
    Private Sub RunAll() 
        ' If we are using an existing datbase, we don't need to create it. 
        If Not CBool(ViewState("UseExistingDb")) Then 
            ' Create the Test database 
            If Not CreateDb() Then 
                Throw New Exception("Error creating database!") 
            End If 
        End If 
        

        ' Add the ASPNETDB tables to the database using the SqlServices Install method. 
        ' This will add the ASPNETDB tables to the same database as the application. 
        ' NOTE: This method can ONLY be used for SQL Server. To point to MySql, 
        ' you will need to create the database scripts for MySql and add them to 
        ' the RunScripts method, but then you need to have the MySql data provider 
        ' set in the web.config. 
        If chkTrustedConnection.Checked Then 
            ' For SQL Server trusted connections 
            System.Web.Management.SqlServices.Install(txtServerName.Text.Trim(), ViewState("DBName").ToString(), System.Web.Management.SqlFeatures.All) 
        Else 
            ' For SQL Server 
            System.Web.Management.SqlServices.Install(txtServerName.Text.Trim(), ViewState("DbUserName").ToString(), ViewState("DbPassword").ToString(), ViewState("DBName").ToString(), System.Web.Management.SqlFeatures.All) 
        End If

        ' Add the tables to the database 
        ' Must be done after membership is installed since Sps rely on the Membership and Roles tables
        RunScripts(ViewState("DBName").ToString())
        
        ' Create the Roles, Administrators, Users. 
        ' You can create whatever roles you need here, just add them to this list or create your own list. 
        ' But I would think that you would need at least the Administrators role. 

        If Not Roles.RoleExists(SYS_ADMINISTRATORS_ROLE) Then
            Roles.CreateRole(SYS_ADMINISTRATORS_ROLE)
        End If
        If Not Roles.RoleExists(ADMINISTRATORS_ROLE) Then
            Roles.CreateRole(ADMINISTRATORS_ROLE)
        End If
        If Not Roles.RoleExists(PROJECTMANGER_ROLE) Then
            Roles.CreateRole(PROJECTMANGER_ROLE)
        End If
        If Not Roles.RoleExists(USERS_ROLE) Then 
            Roles.CreateRole(USERS_ROLE) 
        End If 
        
        ' Create the Admin User 
        Dim status As MembershipCreateStatus = MembershipCreateStatus.UserRejected 
        Dim user As MembershipUser = Membership.CreateUser(txtAdminUserName.Text.Trim(), ViewState("AdminPassword").ToString(), txtAdminEmail.Text.Trim(), txtSecretQuestion.Text.Trim(), txtSecretAnswer.Text.Trim(), True, _ 
        status) 
        
        ' Assign the Admin user to the Administrators role 
        If status = MembershipCreateStatus.Success Then 
            Roles.AddUserToRole(txtAdminUserName.Text, ADMINISTRATORS_ROLE) 
        End If 
        
        '******************************************************************************* 
' * If your app needs custom functions executed after these functions, you need to 
' * add them here. 
' * **************************************************************************** 
        
        
        ' Begin custom routines 
        
        
        
        
        ' End custom routines 
        
    End Sub 
    
    ''' <summary> 
    ''' Add a script event handler to the Finish button to disable it when clicked. 
    ''' </summary> 
    Private Sub AttachEventHandlerToFinishButton() 
        Dim btn As Button = SetupUtility.FindControl(Of Button)(wizInstall, "FinishButton") 
        If btn IsNot Nothing Then 
            btn.Attributes.Add("onclick", "this.value='Processing... please wait!';this.disabled = true;" & Page.ClientScript.GetPostBackEventReference(btn, "")) 
        End If 
    End Sub 
    
    
    #End Region 
    
    #Region "Config Methods" 
    
    ''' <summary> 
    ''' Write the connection string to the web.config file. 
    ''' </summary> 
    Private Sub WriteConnectionStringsToConfig() 
        ' This is the name of the connection string. You can change it to whatever you want. 
        ' This name also needs to be the connectionStringName of the RoleManager and the Membership provider 
        ' sections in the web.config. 
        Dim connStringName As String = "LocalSqlServer"
        
        
        Dim config As Configuration = WebConfigurationManager.OpenWebConfiguration("~") 
        If config Is Nothing Then 
            Throw New InvalidOperationException("Configuration file not available.") 
        End If 
        Dim connectionStringsSection As ConnectionStringsSection = config.ConnectionStrings 
        Dim appTemplate As New ConnectionStringSettings(connStringName, GetConnString(ViewState("DBName").ToString()), "System.Data.SqlClient") 
        connectionStringsSection.ConnectionStrings.Clear() 
        connectionStringsSection.ConnectionStrings.Add(appTemplate) 
        
        ' You can uncomment this if you want to add a LocalSqlServer connection. 
        ' I am connecting to the ASPNETDB tables with the same connection string since they are both in the same database. 
        ' If you create a separate ASPNETDB database, then you can uncomment the following and set the connection string manually. 
        'ConnectionStringSettings LocalSqlServer = new ConnectionStringSettings("LocalSqlServer", GetConnString(ViewState["DBName"].ToString()), "System.Data.SqlClient"); 
        'connectionStringsSection.ConnectionStrings.Add(LocalSqlServer); 
        config.Save() 
    End Sub 
    
    ''' <summary> 
    ''' Optional method to load sample data into the database. 
    ''' </summary> 
    ''' <param name="dbName"></param> 
    Private Sub RunSampleDataScripts(ByVal dbName As String) 
        'SampleData 
        'string[] sampleDataStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(SCRIPT_SAMPLE_DATA), new System.Text.UTF8Encoding())); 
        'ExecuteStatements(sampleDataStatements, dbName); 
    End Sub 
    
    ''' <summary> 
    ''' Run SQL scripts to create the tables and any other sql scripts. 
    ''' </summary> 
    ''' <param name="dbName"></param> 
    Private Sub RunScripts(ByVal dbName As String) 
        'Tables 
        Dim tableStatements As String() = GetScriptStatements(File.ReadAllText(Server.MapPath(SCRIPT_TABLES), New System.Text.UTF8Encoding())) 
        ExecuteStatements(tableStatements, dbName) 
        
        ' Add other sql statements here... such as... !!! Notice the string array variable name is unique 
        ' and it gets passed into the ExecuteStatements method. 
        'string[] newStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(["some other sql scripts"]), new System.Text.UTF8Encoding())); 
        'ExecuteStatements(newStatements, dbName); 
        
        ' Add other sql statements here... such as... 
        'string[] moreStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(["even more sql scripts"]), new System.Text.UTF8Encoding())); 
        'ExecuteStatements(moreStatements, dbName); 
        
    End Sub 
    
    ''' <summary> 
    ''' This will execute the sql statements. 
    ''' </summary> 
    ''' <param name="tableStatements"></param> 
    ''' <param name="dbName"></param> 
    Private Sub ExecuteStatements(ByVal tableStatements As String(), ByVal dbName As String) 
        If tableStatements.Length > 0 Then 
            Using conn As New SqlConnection() 
                conn.ConnectionString = GetConnString(dbName) 
                conn.Open() 
                Using command As New SqlCommand(String.Empty, conn) 
                    For Each statement As String In tableStatements 
                        command.CommandText = statement 
                        command.ExecuteNonQuery() 
                    Next 
                End Using 
            End Using 
        End If 
    End Sub 
    
    ''' <summary> 
    ''' This gets individual script statements from the sql script. 
    ''' </summary> 
    ''' <param name="p"></param> 
    ''' <returns></returns> 
    Private Function GetScriptStatements(ByVal p As String) As String() 
        Dim statements As String() = p.Split(New String() {"GO" & vbCr & vbLf}, StringSplitOptions.RemoveEmptyEntries) 
        Return statements 
    End Function 
    
    ''' <summary> 
    ''' Create the database. 
    ''' </summary> 
    ''' <returns></returns> 
    Private Function CreateDb() As Boolean 
        Dim rowsAffected As Integer = -1 
        Dim dbCreated As Boolean = False 
        Using conn As New SqlConnection() 
            Dim sql As String = "CREATE DATABASE " & txtDBName.Text.Trim() 
            conn.ConnectionString = GetConnString("master") 
            conn.Open() 
            Using cmd As New SqlCommand(sql, conn) 
                rowsAffected = cmd.ExecuteNonQuery() 
                dbCreated = True 
            End Using 
        End Using 
        Return dbCreated 
    End Function 
    
    ''' <summary> 
    ''' Dynamically create a connection string. 
    ''' </summary> 
    ''' <param name="databaseName"></param> 
    ''' <returns></returns> 
    Private Function GetConnString(ByVal databaseName As String) As String 
        Dim connString As String = ("Server=" & txtServerName.Text.Trim() & ";Initial Catalog=") + databaseName & ";" 
        If CBool(chkTrustedConnection.Checked) Then 
            connString += "Integrated Security=true;" 
        Else 
            connString += ("User ID=" & txtDBUsername.Text.Trim() & "; Password=") + ViewState("DbPassword").ToString() & ";" 
        End If 
        Return connString 
    End Function 
    
    
    ''' <summary> 
    ''' Unused method. I find that this method restarts the application immediately, so it creates a loop. 
    ''' I haven't found a work around. 
    ''' </summary> 
    Private Sub TestConfigPermissions() 
        Try 
            Dim config As Configuration = WebConfigurationManager.OpenWebConfiguration("~") 
            If config Is Nothing Then 
                Throw New InvalidOperationException("Configuration file not available.") 
            End If 
            Dim appSettingsSection As AppSettingsSection = TryCast(config.GetSection("appSettings"), AppSettingsSection) 
            Dim appSettings As KeyValueConfigurationCollection = appSettingsSection.Settings 
            If appSettingsSection.SectionInformation.IsLocked Then 
                Throw New InvalidOperationException("Configuration Section is locked. Unable to modify.") 
            End If 
            config.AppSettings.Settings.Add("test", "test") 
            config.Save() 
            config.AppSettings.Settings.Remove("test") 
            config.Save() 
            
        Catch ex As UnauthorizedAccessException 
            wizInstall.ActiveStepIndex = wizInstall.ActiveStepIndex - 1 
            ShowError(ex.Message) 
        End Try 
    End Sub 
    
    
    #End Region 
    
    #Region "Helper Functions" 
    
    ''' <summary> 
    ''' Admin Password needs to be stored in ViewState (or some other persistence mechanism) 
    ''' because it doesn't persist for processing otherwise. 
    ''' </summary> 
    Private Sub CollectAdminInfo() 
        ViewState("AdminUser") = txtAdminUserName.Text.Trim() 
        ViewState("AdminPassword") = txtAdminPassword.Text.Trim() 
    End Sub 
    
    ''' <summary> 
    ''' Load sample data for testing purposes. 
    ''' </summary> 
    Private Sub LoadSampleValues() 
        txtAdminUserName.Text = "admin" 
        txtAdminPassword.Text = "12345" 
        txtAdminPasswordConfirm.Text = "12345" 
        txtAdminEmail.Text = "info@domain.com" 
        txtAdminEmailConfirm.Text = "info@domain.com" 
        txtSecretQuestion.Text = "Favorite Color" 
        txtSecretAnswer.Text = "Orange" 
    End Sub 
    
    ''' <summary> 
    ''' Helper function to display the error. 
    ''' </summary> 
    ''' <param name="message"></param> 
    Private Sub ShowError(ByVal message As String) 
        lblError.Text = message 
    End Sub 
    
    
    #End Region 
    
    #Region "UI Functions" 
    
    
    ''' <summary> 
    ''' Bind the database DropDownList control with the names of the databases. 
    ''' </summary> 
    Private Sub BindDbList() 
        Using conn As New SqlConnection() 
            Dim sql As String = "SELECT * FROM sysdatabases WHERE name NOT IN ('master','tempdb','model','msdb') ORDER BY [NAME]" 
            conn.ConnectionString = GetConnString("master") 
            conn.Open() 
            Using cmd As New SqlCommand(sql, conn) 
                Using rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) 
                    ddlDbList.DataSource = rdr 
                    ddlDbList.DataTextField = "name" 
                    ddlDbList.DataValueField = "name" 
                    ddlDbList.DataBind() 
                    ddlDbList.Items.Insert(0, New ListItem("-- Select --", "-1")) 
                    rdr.Close() 
                End Using 
            End Using 
        End Using 
    End Sub
#End Region
End Class 


